﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Elites
{
    internal static class EliteDAL
    {
        internal static EliteEntity GetEliteByReader(IDataReader dr)
        {
            EliteEntity elite = new EliteEntity();

            elite.EliteID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            elite.Title = dr.IsDBNull(1) ? null : dr.GetString(1);
            elite.Content = dr.IsDBNull(2) ? null : dr.GetString(2);
            elite.Editor = dr.IsDBNull(3) ? null : dr.GetString(3);
            elite.Hits = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            elite.Created = Util.ConverDateTime(dr.GetValue(5));
            elite.Updated = Util.ConverDateTime(dr.GetValue(6));
            elite.Sort = dr.IsDBNull(7) ? 0 : dr.GetInt32(7);
            elite.IsEnable = dr.IsDBNull(8) ? false : dr.GetBoolean(8);
            elite.ProvinceID = dr.IsDBNull(9) ? 0 : dr.GetInt32(9);
            elite.CityID = dr.IsDBNull(10) ? 0 : dr.GetInt32(10);
            elite.ProvinceName = dr.IsDBNull(11) ? "联盟" : dr.GetString(11);
            elite.CityName = dr.IsDBNull(12) ? null : dr.GetString(12);
            elite.ImgUrl = dr.IsDBNull(13) ? null : dr.GetString(13);       

            return elite;
        }

        internal static int GetEliteCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectElite = @"SELECT COUNT([Elite].[EliteID]) 
                FROM Elite 
                    LEFT OUTER JOIN Area AS CityArea ON Elite.CityID = CityArea.AreaID 
                    LEFT OUTER JOIN Area AS ProvinceArea ON Elite.ProvinceID = ProvinceArea.AreaID
                ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectElite, ref parameterlst);
            DbCommand SelectEliteCommand = db.GetSqlStringCommand(SelectElite);
            DbHelper.PackDbCommandByDbParameters(SelectEliteCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectEliteCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<EliteEntity> GetEliteList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<EliteEntity> elitelst = new List<EliteEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectElite = @"
                SELECT [EliteID],[Title],[Content],[Editor],[Hits],[Created],[Updated],[Sort],[IsEnable],
                    [ProvinceID],[CityID],[ProvinceName],[CityName],[ImgUrl]
                FROM (
                    SELECT Elite.EliteID, Elite.Title, Elite.[Content], 
                        Elite.Editor, Elite.Hits, Elite.Created, Elite.Updated, Elite.Sort, Elite.IsEnable,Elite.[ProvinceID], 
                        Elite.[CityID], ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName,Elite.[ImgUrl],
                        ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectElite += " " + sortField + " " + sortMode + ",";
            SelectElite += @" Elite.[EliteID] Desc
                    ) AS rowNum 
                        FROM Elite 
                            LEFT OUTER JOIN Area AS CityArea ON Elite.CityID = CityArea.AreaID 
                            LEFT OUTER JOIN Area AS ProvinceArea ON Elite.ProvinceID = ProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectElite, ref parameterlst);
            SelectElite += @"
                ) AS [elite] 
                WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectEliteCommand = db.GetSqlStringCommand(SelectElite);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectEliteCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectEliteCommand))
            {
                while (dr.Read())
                {
                    EliteEntity elite = GetEliteByReader(dr);

                    elitelst.Add(elite);
                }
            }

            return elitelst;
        }

        internal static EliteEntity GetElite(int eliteID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            EliteEntity elite = null;

            string SelectElite = @"
                    SELECT Elite.EliteID, Elite.Title, Elite.[Content], 
                        Elite.Editor, Elite.Hits, Elite.Created, Elite.Updated, Elite.Sort, Elite.IsEnable,  
                        Elite.[ProvinceID], Elite.[CityID], ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName,Elite.[ImgUrl]
                    FROM Elite 
                        LEFT OUTER JOIN Area AS CityArea ON Elite.CityID = CityArea.AreaID 
                        LEFT OUTER JOIN Area AS ProvinceArea ON Elite.ProvinceID = ProvinceArea.AreaID
                    WHERE Elite.EliteID=@EliteID
                ";

            DbCommand SelectEliteCommand = db.GetSqlStringCommand(SelectElite);
            db.AddInParameter(SelectEliteCommand, "@EliteID", DbType.Int32, eliteID);

            using (IDataReader dr = db.ExecuteReader(SelectEliteCommand))
            {
                if (dr.Read())
                {
                    elite = GetEliteByReader(dr);
                }
            }

            return elite;
        }

        internal static int InsertElite(EliteEntity elite)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertElite = @"INSERT INTO [Elite] 
				([Title],[Content],[Editor],[Created],[Updated],[Sort],
                [IsEnable],[Hits],[ProvinceID],[CityID],[ImgUrl])
				 VALUES (@Title,@Content,@Editor,@Created,@Updated,@Sort,
                @IsEnable,@Hits,@ProvinceID,@CityID,@ImgUrl)";

            DbCommand InsertEliteCommand = db.GetSqlStringCommand(InsertElite);
            db.AddInParameter(InsertEliteCommand, "@Title", DbType.String, elite.Title);
            db.AddInParameter(InsertEliteCommand, "@Content", DbType.String, elite.Content);
            db.AddInParameter(InsertEliteCommand, "@Editor", DbType.String, elite.Editor);
            db.AddInParameter(InsertEliteCommand, "@Created", DbType.DateTime, elite.Created);
            db.AddInParameter(InsertEliteCommand, "@Updated", DbType.DateTime, elite.Updated);
            db.AddInParameter(InsertEliteCommand, "@Sort", DbType.Int32, elite.Sort);
            db.AddInParameter(InsertEliteCommand, "@IsEnable", DbType.Boolean, elite.IsEnable);
            db.AddInParameter(InsertEliteCommand, "@Hits", DbType.Int32, elite.Hits);
            db.AddInParameter(InsertEliteCommand, "@ProvinceID", DbType.Int32, elite.ProvinceID);
            db.AddInParameter(InsertEliteCommand, "@CityID", DbType.Int32, elite.CityID);
            db.AddInParameter(InsertEliteCommand, "@ImgUrl", DbType.String, elite.ImgUrl);

            result = db.ExecuteNonQuery(InsertEliteCommand);

            return result;
        }

        internal static int UpdateElite(EliteEntity elite)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateElite = @"UPDATE [Elite] 
				 SET [Title]=@Title,[Content]=@Content,[Editor]=@Editor,[Updated]=@Updated,
                    [Sort]=@Sort,[IsEnable]=@IsEnable,[Hits]=@Hits,[ProvinceID]=@ProvinceID,[CityID]=@CityID,[ImgUrl]=@ImgUrl
				 WHERE [EliteID] = @EliteID";

            DbCommand UpdateEliteCommand = db.GetSqlStringCommand(UpdateElite);
            db.AddInParameter(UpdateEliteCommand, "@EliteID", DbType.Int32, elite.EliteID);
            db.AddInParameter(UpdateEliteCommand, "@Title", DbType.String, elite.Title);
            db.AddInParameter(UpdateEliteCommand, "@Content", DbType.String, elite.Content);
            db.AddInParameter(UpdateEliteCommand, "@Editor", DbType.String, elite.Editor);
            db.AddInParameter(UpdateEliteCommand, "@Updated", DbType.DateTime, elite.Updated);
            db.AddInParameter(UpdateEliteCommand, "@Sort", DbType.Int32, elite.Sort);
            db.AddInParameter(UpdateEliteCommand, "@IsEnable", DbType.Boolean, elite.IsEnable);
            db.AddInParameter(UpdateEliteCommand, "@Hits", DbType.Int32, elite.Hits);
            db.AddInParameter(UpdateEliteCommand, "@ProvinceID", DbType.Int32, elite.ProvinceID);
            db.AddInParameter(UpdateEliteCommand, "@CityID", DbType.Int32, elite.CityID);
            db.AddInParameter(UpdateEliteCommand, "@ImgUrl", DbType.String, elite.ImgUrl);

            result = db.ExecuteNonQuery(UpdateEliteCommand);

            return result;
        }

        internal static int DeleteElite(int eliteID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteElite = @"
                DELETE FROM [Elite] WHERE EliteID=@EliteID";

            DbCommand DeleteEliteCommand = db.GetSqlStringCommand(DeleteElite);
            db.AddInParameter(DeleteEliteCommand, "@EliteID", DbType.Int32, eliteID);
            result = db.ExecuteNonQuery(DeleteEliteCommand);

            return result;
        }

        internal static int SetEliteEnable(int eliteID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetElite = @"UPDATE [Elite]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [EliteID] = @EliteID";

            DbCommand SetEliteCommand = db.GetSqlStringCommand(SetElite);
            db.AddInParameter(SetEliteCommand, "@EliteID", DbType.Int32, eliteID);
            result = db.ExecuteNonQuery(SetEliteCommand);

            return result;
        }

        internal static int HitElite(int eliteID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetElite = @"UPDATE [Elite]
                    SET [Hits] = [Hits]+1
                    WHERE [EliteID] = @EliteID";

            DbCommand SetEliteCommand = db.GetSqlStringCommand(SetElite);
            db.AddInParameter(SetEliteCommand, "@EliteID", DbType.Int32, eliteID);
            result = db.ExecuteNonQuery(SetEliteCommand);

            return result;
        }
    }
}
